package com.alks.function.mapper.administration;

import com.alks.entity.data.entity.*;
import com.alks.function.data.dto.administration.*;
import com.alks.function.data.request.administration.*;
import com.alks.function.data.request.finance.salary.UpdateRemarkRequest;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.annotations.Param;
import org.mapstruct.Mapper;

import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;

@Mapper
public interface PcEmployeeMapper extends BaseMapper<PcEmployee> {

    /**
     * 查询员工资料
     * @param request
     * @return
     */
    List<PcEmployeeDto> getEmployeeAll(PcEmployeeRequest request);

    /**
     * 职务查询
     * @return
     */
    @Select("select POSITION_SEQ as positionSeq,POSITION as position from PC_EMPLOYEE_POSITION order by positionSeq asc")
    List<Map<String,String>> getDutiesList();

    /**
     * 添加职务
     * @param pcEmployeePosition
     */
    @Insert("INSERT INTO PC_EMPLOYEE_POSITION (COMPANY_ID, POSITION, SYS_USER, SYS_DATE)VALUES (#{companyId}, #{position}, #{sysUser}, #{sysDate})")
    void addDuties(PcEmployeePosition pcEmployeePosition);

    /**
     * 查询单个职务
     * @param positionSeq
     * @return
     */
    @Select("select POSITION from PC_EMPLOYEE_POSITION where POSITION_SEQ=#{positionSeq}")
    String getDuties(String positionSeq);

    /**
     * 查询是否被员工表引用
     * @param position
     * @return
     */
    @Select("select count(1) from PC_EMPLOYEE where POSITION=#{position}")
    Integer getPositionToPcEmployee(String position);

    /**
     * 判断是否在员工待确认表中
     * @param position
     * @return
     */
    @Select("select count(1) from PC_EMPLOYEE_TEMP where POSITION=#{position}")
    Integer getPositionToPcEmployeeTemp(String position);

    /**
     * 删除职务
     * @param positionSeq
     */
    @Delete("delete from PC_EMPLOYEE_POSITION where POSITION_SEQ=#{positionSeq}")
    void delDuties(String positionSeq);

    /**
     * 更新职务
     * @param positionSeq
     * @param position
     */
    @Update("update PC_EMPLOYEE_POSITION set POSITION=#{position} where POSITION_SEQ=#{positionSeq}")
    void updateDuties(@Param("positionSeq") String positionSeq,@Param("position") String position);

    /**
     * 查询员工信息是否在带确认员工资料中存在
     * @param idNo
     */
    @Select("select * FROM PC_EMPLOYEE_TEMP WHERE ID_NO=#{idNo} AND COMPANY_ID=#{companyId}")
    PcEmployeeTemp getEmployeeTemp(@Param("idNo") String idNo, @Param("companyId") String companyId);

    /**
     * 添加员工信息到待确认资料中
     * @param pcEmployeeTemp
     */
    @Insert(" INSERT INTO PC_EMPLOYEE_TEMP (COMPANY_ID, DEPT_NO, DEPT_NAME, NAME, SEX, ID_NO, POSITION, WORK_TYPE, MOBILE_NO, ADDRESS, DATE_IN, CONTRACT, SYS_USER, SYS_DATE, CALC_TYPE, WORKER_ID, EDUCATION, MARRIED, CHILD_FLAG, NATION, CONTACT_PERSON, CONTACT_PHONE, ADDRESS_NOW, BANK_CARD_NAME, BANK_CARD_ID, BANK_CARD_USER,BRISDAY)" +
            "VALUES (#{companyId}, #{deptNo}, #{deptName}, #{name}, #{sex}, #{idNo}, #{position}, #{workType}, #{mobileNo}, #{address}, #{dateIn}, #{contract}, #{sysUser}, #{sysDate}, #{calcType}, #{workerId}, #{education}, #{married}, #{childFlag}, #{nation}, #{contactPerson}, #{contactPhone}, #{addressNow}, #{bankCardName}, #{bankCardId}, #{bankCardUser},#{brisday})")
    void addPcEmployeeTemp(PcEmployeeTemp pcEmployeeTemp);

    /**
     * 查询员工删除记录中工号
     */
    @Select("SELECT max(WORKER_ID) from PC_EMPLOYEE_DEL where COMPANY_ID=#{companyId}")
    String getEmployeeDelWokeId(String companyId);

    /**
     * 查询员工资料中工号
     */
    @Select("SELECT max(WORKER_ID) from PC_EMPLOYEE where COMPANY_ID=#{companyId}")
    String getEmployeeWokeId(String companyId);

    /**
     * 查询员工资料待确认中工号
     */
    @Select("select WORKER_ID from PC_EMPLOYEE_TEMP where WORKER_ID=#{workId}")
    String getEmployeeTempWokeId(String workId);

    /**
     * 查询工种
     * @param request
     */
    @Select({
            "<script>",
            "select WORK_TYPE_SEQ, WORK_TYPE, WORK_TYPE_SALARY,WORK_TYPE_CATEGORY,WORK_TYPE_SHORT,CALC_TYPE from PC_EMPLOYEE_WORK_TYPE",
            "<where>",
            "<if test='companyId!=null and !companyId.isEmpty()'>",
            " and COMPANY_ID=#{companyId}",
            "</if>",
            "<if test='workType!=null and !workType.isEmpty()'>",
            " and WORK_TYPE LIKE concat('%',#{workType},'%')",
            "</if>",
            "</where>",
            "</script>"
    })
    List<PcEmployeeWokeTypeDto> getEmployeeWokeType(PcEmployeeWokeTypeRequest request);

    /**
     * 查询工种是否存在
     * @param workType
     * @return
     */
    @Select("select WORK_TYPE from PC_EMPLOYEE_WORK_TYPE where WORK_TYPE=#{workType}")
    String getWorkTypToPcEmployeeWorkType(String workType);

    /**
     * 工种新增
     * @param pcEmployeeWorkType
     */
    @Insert("INSERT INTO PC_EMPLOYEE_WORK_TYPE(COMPANY_ID, WORK_TYPE, SYS_USER, SYS_DATE, WORK_TYPE_SHORT, WORK_TYPE_SALARY, WORK_TYPE_CATEGORY, CALC_TYPE)" +
            " VALUES(#{companyId}, #{workType}, #{sysUser}, #{sysDate}, #{workTypeShort}, #{workTypeSalary}, #{workTypeCategory}, #{calcType})")
    void addPcEmployeeWorkType(PcEmployeeWorkType pcEmployeeWorkType);

    /**
     * 查询在员工表中是否有该工种
     * @param workType
     */
    @Select("select count(1) from PC_EMPLOYEE where WORK_TYPE=#{workType}")
    Integer getWorkTypToPcEmployee(String workType);

    /**
     * 查询在待确认员工表中是否有该工种
     * @param workType
     */
    @Select("select count(1) from PC_EMPLOYEE_TEMP where WORK_TYPE=#{workType}")
    Integer getWorkTypToPcEmployeeTemp(String workType);

    /**
     * 修改工种
     * @param request
     */
    @Update("update PC_EMPLOYEE_WORK_TYPE set WORK_TYPE=#{workType},WORK_TYPE_SALARY=#{workTypeSalary},WORK_TYPE_CATEGORY=#{workTypeCategory},CALC_TYPE=#{caleType} where WORK_TYPE_SEQ=#{workTypeSeq}")
    void updatePcEmployeeWorkType(PcEmployeeWokeTypeAddRequest request);

    /**
     * 根据序号返回工种
     * @param workTypeSeq
     * @return
     */
    @Select("select WORK_TYPE from PC_EMPLOYEE_WORK_TYPE where WORK_TYPE_SEQ=#{workTypeSeq}")
    String getPcEmployeeWorkType(String workTypeSeq);

    /**
     * 删除工种
     * @param workTypeSeq
     */
    @Delete("delete from PC_EMPLOYEE_WORK_TYPE where WORK_TYPE_SEQ=#{workTypeSeq}")
    void delPcEmployeeWorkType(String workTypeSeq);

    /**
     * 根据工种查询工资
     * @param workType
     * @return
     */
    @Select("select WORK_TYPE_SALARY from PC_EMPLOYEE_WORK_TYPE where WORK_TYPE=#{workType}")
    BigDecimal getPcEmployeeWorkTypeToSalary(String workType);

    /**
     * 根据员工编号查询基本工资
     * @param workerId
     * @return
     */
    @Select("select BASIC_SALARY from PC_BASIC_SALARY where WORKER_ID=#{workerId}")
    BigDecimal getPcEmployeeBasicToSalary(String workerId);

    /**
     * 删除员工临时表中数据
     * @param idNo
     */
    @Delete("delete from PC_EMPLOYEE_TEMP where ID_NO=#{idNo}")
    void delPcEmployeeTemp(String idNo);

    /**
     * 查询员工临时表
     * @return
     */
    @Select("select DEPT_NAME,WORKER_ID,NAME,SEX,ID_NO,POSITION,WORK_TYPE,CALC_TYPE,ADDRESS,DATE_IN,BANK_CARD_USER,BANK_CARD_NAME,BANK_CARD_ID," +
            "CONTRACT,EDUCATION,MARRIED,CONTACT_PERSON,CONTACT_PHONE,ADDRESS_NOW,CHILD_FLAG,MOBILE_NO,NATION from PC_EMPLOYEE_TEMP where COMPANY_ID=#{companyId}")
    List<PcEmployeeTempDto> getEmployeeTempDetails(String companyId);

    /**
     * 修改员工临时表
     * @param request
     */
    @Update("UPDATE PC_EMPLOYEE_TEMP set DEPT_NO = #{deptNo},DEPT_NAME = #{deptName},NAME = #{name},SEX = #{sex},POSITION = #{position}," +
            "WORK_TYPE = #{workType},MOBILE_NO = #{mobileNo},ADDRESS = #{address},DATE_IN = #{dateIn},CONTRACT = #{contract},SYS_USER = #{sysUser}," +
            "SYS_DATE = #{sysDate},CALC_TYPE = #{calcType},EDUCATION = #{education},MARRIED = #{married},CHILD_FLAG = #{childFlag}," +
            "NATION = #{nation},CONTACT_PERSON = #{contactPerson},CONTACT_PHONE = #{contactPhone},ADDRESS_NOW = #{addressNow},BANK_CARD_NAME = #{bankCardName}" +
            ",BANK_CARD_ID = #{bankCardId},BANK_CARD_USER = #{bankCardUser} WHERE ID_NO = #{idNo} and COMPANY_ID=#{companyId} ")
    void updatePcEmployeeTemp(PcEmployeeTemp request);

    /**
     * 员工资料明细
     * @param request
     * @return
     */
    @Select({
            "<script>",
            " SELECT DEPT_NAME,WORKER_ID,NAME,SEX,ID_NO,BRISDAY,POSITION,WORK_TYPE,CALC_TYPE,MOBILE_NO,ADDRESS,DATE_IN,BANK_CARD_USER," +
            "BANK_CARD_NAME,BANK_CARD_ID,CONTRACT,EDUCATION,NATION,MARRIED,CONTACT_PERSON,CONTACT_PHONE,ADDRESS_NOW,CHILD_FLAG FROM PC_EMPLOYEE",
            "<where>",
            "<if test='companyId!=null and !companyId.isEmpty()'>",
            " and COMPANY_ID=#{companyId}",
            "</if>",
            "<if test='deptName!=null and deptName.size()>0'>",
            " and DEPT_NAME in <foreach item=\"item\" index=\"index\" collection=\"deptName\"\n" +
                    " open=\"(\" separator=\",\" close=\")\">\n" +
                    " #{item, jdbcType=VARCHAR}\n" +
                    "</foreach>",
            "</if>",
            "<if test='deptNo!=null and !deptNo.isEmpty()'>",
            " and DEPT_NO LIKE concat('%',#{deptNo},'%')",
            "</if>",
            "<if test='name!=null and !name.isEmpty()'>",
            " and NAME LIKE concat('%',#{name},'%')",
            "</if>",
            "<if test='sex!=null and !sex.isEmpty()'>",
            " and SEX=#{sex}",
            "</if>",
            "<if test='idNo!=null and !idNo.isEmpty()'>",
            " and ID_NO LIKE concat('%',#{idNo},'%')",
            "</if>",
            "<if test='position!=null and !position.isEmpty()'>",
            " and POSITION LIKE concat('%',#{position},'%')",
            "</if>",
            "<if test='workType!=null and !workType.isEmpty()'>",
            " and WORK_TYPE LIKE concat('%',#{workType},'%')",
            "</if>",
            "<if test='calcType!=null and !calcType.isEmpty()'>",
            " and CALC_TYPE=#{calcType}",
            "</if>",
            "<if test='inTime!=null and outTime!=null'>",
            "and DATE_IN BETWEEN #{inTime} AND #{outTime}",
            "</if>",
            "<if test='inTime!=null and outTime==null'>",
            "and DATE_IN <![CDATA[ >=]]> #{inTime}",
            "</if>",
            "<if test='outTime!=null and inTime==null'>",
            "and DATE_IN <![CDATA[ <=]]> #{outTime}",
            "</if>",
            "<if test='inBirthday!=null and outBirthday!=null'>",
            "and BRISDAY BETWEEN #{inBirthday} AND #{outBirthday}",
            "</if>",
            "<if test='inBirthday!=null and outBirthday==null'>",
            "and BRISDAY <![CDATA[ >=]]> #{inBirthday}",
            "</if>",
            "<if test='outBirthday!=null and inBirthday==null'>",
            "and BRISDAY <![CDATA[ <=]]> #{outBirthday}",
            "</if>",
            "<if test='bankCardUser!=null and !bankCardUser.isEmpty()'>",
            "and BANK_CARD_USER LIKE CONCAT('%',#{bankCardUser},'%')",
            "</if>",
            "<if test='bankCardName!=null and !bankCardName.isEmpty()'>",
            "and BANK_CARD_NAME LIKE CONCAT('%',#{bankCardName},'%')",
            "</if>",
            "<if test='bankCardId!=null and !bankCardId.isEmpty()'>",
            "and BANK_CARD_ID LIKE CONCAT('%',#{bankCardId},'%')",
            "</if>",
            "<if test='address!=null and !address.isEmpty()'>",
            "and ADDRESS LIKE CONCAT('%',#{address},'%')",
            "</if>",
            "<if test='contract!=null and !contract.isEmpty()'>",
            "and CONTRACT LIKE CONCAT('%',#{contract},'%')",
            "</if>",
            "</where>",
            "order by WORKER_ID asc",
            "</script>",
    })
    List<PcEmployeeListDto> getPcEmployeeList(PcEmployeeRequest2 request);

    /**
     * 插入奖惩资料
     * @param pcEmployeeAward
     */
    @Insert(" INSERT INTO PC_EMPLOYEE_AWARD (COMPANY_ID, DEPT_NO, DEPT_NAME, NAME, ID_NO, WORKER_ID, DDATE, TYPE, NUMS, REASON, SYS_DATE, SYS_USER, POSITION)" +
            "VALUES (#{companyId}, #{deptNo}, #{deptName}, #{name}, #{idNo}, #{workerId}, #{ddate}, #{type}, #{nums}, #{reason}, #{sysDate}, #{sysUser}, #{position})")
    void addPcEmployeeAward(PcEmployeeAward pcEmployeeAward);

    /**
     * 查询奖惩资料
     * @param request
     */
    @Select({
            "<script>",
            " SELECT  ID,DEPT_NAME,NAME,ID_NO,WORKER_ID,DDATE,TYPE,NUMS,REASON,POSITION FROM PC_EMPLOYEE_AWARD",
            "<where>",
            "<if test='workerId!=null and !workerId.isEmpty()'>",
            " and WORKER_ID LIKE concat('%',#{workId},'%')",
            "</if>",
            "<if test='deptName!=null and !deptName.isEmpty()'>",
            " and DEPT_NAME LIKE concat('%',#{deptName},'%')",
            "</if>",
            "<if test='name!=null and !name.isEmpty()'>",
            " and NAME LIKE concat('%',#{name},'%')",
            "</if>",
            "<if test='ddateStart!=null and ddateEnd!=null'>",
            "and DDATE BETWEEN #{ddateStart} AND #{ddateEnd}",
            "</if>",
            "<if test='ddateStart!=null and ddateEnd==null'>",
            "and DDATE <![CDATA[ >=]]> #{ddateStart}",
            "</if>",
            "<if test='ddateEnd!=null and ddateStart==null'>",
            "and DDATE <![CDATA[ <=]]> #{ddateEnd}",
            "</if>",
            "<if test='type!=null and !type.isEmpty()'>",
            "and TYPE=#{type}",
            "</if>",
            "</where>",
            "</script>",
    })
    List<PcEmployeeAwardDto> getPcEmployeeAward(PcEmployeeAwardGetRequest request);

    @Select("select SYS_USER from PC_EMPLOYEE_AWARD where ID=#{id} and SYS_USER=#{userName}")
    String getPcEmployeeAwardExist(@Param("id") Integer id,@Param("userName") String userName);

    /**
     * 删除奖惩资料
     * @param id
     * @param userName
     */
    @Delete("delete from PC_EMPLOYEE_AWARD where ID=#{id} and SYS_USER=#{userName}")
    void delPcEmployeeAward(@Param("id") Integer id,@Param("userName") String userName);

    /**
     * 查询原因列表
     * @param companyId
     * @return
     */
    @Select("select DEL_SEQ as delSeq,DEL_REASON as delReason from PC_EMPLOYEE_DEL_REASON where COMPANY_ID=#{companyId}")
    List<Map<String,String>> getPcEmployeeDelReason(String companyId);

    /**
     * 查询序号是否存在
     * @param delSeq
     * @param companyId
     * @return
     */
    @Select("select DEL_SEQ  from PC_EMPLOYEE_DEL_REASON where COMPANY_ID=#{companyId} and DEL_SEQ=#{delSeq}")
    String getPcEmployeeDelReasonByDelSeq(@Param("delSeq") String delSeq,@Param("companyId") String companyId);

    /**
     * 查询离职原因是否存在
     * @param delReason
     * @param companyId
     * @return
     */
    @Select("select DEL_REASON  from PC_EMPLOYEE_DEL_REASON where COMPANY_ID=#{companyId} and DEL_REASON=#{delReason}")
    String getPcEmployeeDelReasonByDelReason(@Param("delReason")String delReason,@Param("companyId") String companyId);

    /**
     * 离职原因插入
     * @param pcEmployeeDelReason
     */
    @Insert(" INSERT INTO PC_EMPLOYEE_DEL_REASON (COMPANY_ID, DEL_SEQ, DEL_REASON, SYS_USER, SYS_DATE) VALUES (#{companyId}, #{delSeq}, #{delReason}, #{sysUser}, #{sysDate})")
    void addPcEmployeeDelReason(PcEmployeeDelReason pcEmployeeDelReason);

    /**
     * 判断是否被引用
     * @param delReason
     * @return
     */
    @Select("select count(0) from PC_EMPLOYEE_DEL where DEL_REASON=#{delReason}")
    Integer getReasonToPcEmployeeDel(String delReason);

    /**
     * 离职原因删除
     * @param companyId
     * @param delReason
     */
    @Delete("delete from PC_EMPLOYEE_DEL_REASON where COMPANY_ID=#{companyId} and DEL_REASON=#{delReason} ")
    void delPcEmployeeDelReason(@Param("companyId") String companyId,@Param("delReason") String delReason);

    /**
     * 部门异动记录表
     * @param list
     *
     */
    @Insert({
            "<script>",
            "INSERT INTO PC_EMPLOYEE_DEPT_CHG (COMPANY_ID, DEPT_NO, DEPT_NAME, NAME, SEX, ID_NO, POSITION, WORK_TYPE, CALC_TYPE, MOBILE_NO, ADDRESS, DATE_IN, CONTRACT, SYS_USER, SYS_DATE, NEW_DEPT_NO, NEW_DEPT_NAME)",
            "VALUES",
            "<foreach item='t' collection='list' separator=','>",
            "(#{t.companyId}, #{t.deptNo}, #{t.deptName}, #{t.name}, #{t.sex}, #{t.idNo}, #{t.position}, #{t.workType}, #{t.calcType}, #{t.mobileNo}, #{t.address}, #{t.dateIn}, #{t.contract}, #{t.sysUser},#{t.sysDate}, #{t.newDeptNo}, #{t.newDeptName})",
            "</foreach>",
            "</script>"
    })
    void addPcEmployeeDeptChg(List<PcEmployeeDeptChg> list);

    /**
     * 更新部门信息
     * @param deptNo
     * @param deptName
     * @param workerId
     */
    @Update({
            "<script>",
            "update PC_EMPLOYEE set DEPT_NO=#{deptNo},DEPT_NAME=#{deptName} where WORKER_ID in ",
            "<foreach item='t' collection='workerId' open='(' separator=',' close=')'>",
            "#{t}",
            "</foreach>",
            "</script>"
    })
    void updateDeptToPcEmployee(@Param("deptNo") String deptNo,@Param("deptName") String deptName,@Param("workerId") List<String> workerId);

    /**
     * 部门异动资料查询
     * @param request
     * @return
     */
    @Select({
            "<script>",
            " SELECT SYS_DATE,NEW_DEPT_NAME as `change`,DEPT_NAME,NAME,SEX,ID_NO,POSITION,WORK_TYPE,CALC_TYPE,MOBILE_NO,ADDRESS,DATE_IN,CONTRACT FROM PC_EMPLOYEE_DEPT_CHG",
            "<where>",
            "<if test='companyId!=null and !companyId.isEmpty()'>",
            " and COMPANY_ID=#{companyId}",
            "</if>",
            "<if test='deptName!=null and deptName.size()>0'>",
            " and DEPT_NAME in <foreach item=\"item\" index=\"index\" collection=\"deptName\"\n" +
                    " open=\"(\" separator=\",\" close=\")\">\n" +
                    " #{item, jdbcType=VARCHAR}\n" +
                    "</foreach>",
            "</if>",
            "<if test='name!=null and !name.isEmpty()'>",
            " and NAME LIKE concat('%',#{name},'%')",
            "</if>",
            "<if test='sex!=null and !sex.isEmpty()'>",
            " and SEX=#{sex}",
            "</if>",
            "<if test='idNo!=null and !idNo.isEmpty()'>",
            " and ID_NO LIKE concat('%',#{idNo},'%')",
            "</if>",
            "<if test='position!=null and !position.isEmpty()'>",
            " and POSITION LIKE concat('%',#{position},'%')",
            "</if>",
            "<if test='workType!=null and !workType.isEmpty()'>",
            " and WORK_TYPE LIKE concat('%',#{workType},'%')",
            "</if>",
            "<if test='calcType!=null and !calcType.isEmpty()'>",
            " and CALC_TYPE=#{calcType}",
            "</if>",
            "<if test='inTime!=null and outTime!=null'>",
            "and SYS_DATE BETWEEN #{inTime} AND #{outTime}",
            "</if>",
            "<if test='inTime!=null and outTime==null'>",
            "and SYS_DATE <![CDATA[ >=]]> #{inTime}",
            "</if>",
            "<if test='outTime!=null and inTime==null'>",
            "and SYS_DATE <![CDATA[ <=]]> #{outTime}",
            "</if>",
            "<if test='address!=null and !address.isEmpty()'>",
            "and ADDRESS LIKE CONCAT('%',#{address},'%')",
            "</if>",
            "<if test='change!=null and !change.isEmpty()'>",
            " and NEW_DEPT_NAME LIKE concat('%',#{change},'%')",
            "</if>",
            "<if test='newDeptName!=null and newDeptName.size()>0'>",
            " and NEW_DEPT_NAME in <foreach item=\"item\" index=\"index\" collection=\"newDeptName\"\n" +
                    " open=\"(\" separator=\",\" close=\")\">\n" +
                    " #{item, jdbcType=VARCHAR}\n" +
                    "</foreach>",
            "</if>",
            "</where>",
            "order by SYS_DATE desc",
            "</script>",
    })
    List<ChangeDto> getPcEmployeeDeptChgList(GetRequest request);

    /**
     * 职务变更记录表
     * @param list
     */
    @Insert({
            "<script>",
            "INSERT INTO PC_EMPLOYEE_POSITION_CHG(COMPANY_ID, DEPT_NO, DEPT_NAME, NAME, SEX, ID_NO, POSITION, WORK_TYPE, CALC_TYPE, MOBILE_NO, ADDRESS, DATE_IN, CONTRACT, SYS_USER, SYS_DATE, NEW_POSITION)",
            "VALUES",
            "<foreach item='t' collection='list' separator=','>",
            "(#{t.companyId}, #{t.deptNo}, #{t.deptName}, #{t.name}, #{t.sex}, #{t.idNo}, #{t.position}, #{t.workType}, #{t.calcType}, #{t.mobileNo}, #{t.address}, #{t.dateIn}, #{t.contract}, #{t.sysUser}, #{t.sysDate}, #{t.newPosition})",
            "</foreach>",
            "</script>"
    })
    void addPcEmployeePositionChg(@Param("list") List<PcEmployeePositionChg> list);

    /**
     * 更新职务信息
     * @param position
     * @param workerId
     */
    @Update({
            "<script>",
            "update PC_EMPLOYEE set POSITION = #{position} where WORKER_ID in ",
            "<foreach item='t' collection='workerId' open='(' separator=',' close=')'>",
            "#{t}",
            "</foreach>",
            "</script>"
    })
    void updatePositionToPcEmployee(@Param("position") String position,@Param("workerId") List<String> workerId);

    /**
     * 职务变更资料查询
     * @param request
     * @return
     */
    @Select({
            "<script>",
            " SELECT SYS_DATE,NEW_POSITION as `change`,DEPT_NAME,NAME,SEX,ID_NO,POSITION,WORK_TYPE,CALC_TYPE,MOBILE_NO,ADDRESS,DATE_IN,CONTRACT FROM PC_EMPLOYEE_POSITION_CHG",
            "<where>",
            "<if test='companyId!=null and !companyId.isEmpty()'>",
            " and COMPANY_ID=#{companyId}",
            "</if>",
            "<if test='deptName!=null and deptName.size()>0'>",
            " and DEPT_NAME in <foreach item=\"item\" index=\"index\" collection=\"deptName\"\n" +
                    " open=\"(\" separator=\",\" close=\")\">\n" +
                    " #{item, jdbcType=VARCHAR}\n" +
                    "</foreach>",
            "</if>",
            "<if test='name!=null and !name.isEmpty()'>",
            " and NAME LIKE concat('%',#{name},'%')",
            "</if>",
            "<if test='sex!=null and !sex.isEmpty()'>",
            " and SEX=#{sex}",
            "</if>",
            "<if test='idNo!=null and !idNo.isEmpty()'>",
            " and ID_NO LIKE concat('%',#{idNo},'%')",
            "</if>",
            "<if test='position!=null and !position.isEmpty()'>",
            " and POSITION LIKE concat('%',#{position},'%')",
            "</if>",
            "<if test='workType!=null and !workType.isEmpty()'>",
            " and WORK_TYPE LIKE concat('%',#{workType},'%')",
            "</if>",
            "<if test='calcType!=null and !calcType.isEmpty()'>",
            " and CALC_TYPE=#{calcType}",
            "</if>",
            "<if test='inTime!=null and outTime!=null'>",
            "and SYS_DATE BETWEEN #{inTime} AND #{outTime}",
            "</if>",
            "<if test='inTime!=null and outTime==null'>",
            "and SYS_DATE <![CDATA[ >=]]> #{inTime}",
            "</if>",
            "<if test='outTime!=null and inTime==null'>",
            "and SYS_DATE <![CDATA[ <=]]> #{outTime}",
            "</if>",
            "<if test='address!=null and !address.isEmpty()'>",
            "and ADDRESS LIKE CONCAT('%',#{address},'%')",
            "</if>",
            "<if test='change!=null and !change.isEmpty()'>",
            " and NEW_POSITION LIKE concat('%',#{change},'%')",
            "</if>",
            "</where>",
            "order by SYS_DATE desc",
            "</script>",
    })
    List<ChangeDto> getPcEmployeePositionChgList(GetRequest request);

    /**
     * 工种变更记录
     * @param list
     */
    @Insert({
            "<script>",
            "INSERT INTO PC_EMPLOYEE_WORK_TYPE_CHG (COMPANY_ID, DEPT_NO, DEPT_NAME, NAME, SEX, ID_NO, POSITION, WORK_TYPE, CALC_TYPE, MOBILE_NO, ADDRESS, DATE_IN, CONTRACT, SYS_USER, SYS_DATE, NEW_WORK_TYPE, NEW_CALC_TYPE, OLD_BASIC_SALARY)",
            "VALUES",
            "<foreach item='t' collection='list' separator=','>",
            "(#{t.companyId}, #{t.deptNo}, #{t.deptName}, #{t.name}, #{t.sex}, #{t.idNo}, #{t.position}, #{t.workType}, #{t.calcType}, #{t.mobileNo}, #{t.address}, #{t.dateIn}, #{t.contract}, #{t.sysUser}, #{t.sysDate}, #{t.newWorkType}, #{t.newCalcType}, #{t.oldBasicSalary})",
            "</foreach>",
            "</script>"
    })
    void addPcEmployeeWorkTypeChg(List<PcEmployeeWorkTypeChg> list);

    /**
     * 更新工种信息
     * @param workType
     * @param workerId
     */
    @Update({
            "<script>",
            "update PC_EMPLOYEE set WORK_TYPE = #{workType} where WORKER_ID in ",
            "<foreach item='t' collection='workerId' open='(' separator=',' close=')'>",
            "#{t}",
            "</foreach>",
            "</script>"
    })
    void updateWorkTypeToPcEmployee(@Param("workType") String workType,@Param("workerId") List<String> workerId);

    /**
     * 工种变更资料查询
     * @param request
     * @return
     */
    @Select({
            "<script>",
            " SELECT SYS_DATE,NEW_WORK_TYPE as `change`,NEW_CALC_TYPE,DEPT_NAME,NAME,SEX,ID_NO,POSITION,WORK_TYPE,CALC_TYPE,OLD_BASIC_SALARY,ADDRESS,DATE_IN,CONTRACT FROM PC_EMPLOYEE_WORK_TYPE_CHG",
            "<where>",
            "<if test='companyId!=null and !companyId.isEmpty()'>",
            " and COMPANY_ID=#{companyId}",
            "</if>",
            "<if test='deptName!=null and deptName.size()>0'>",
            " and DEPT_NAME in <foreach item=\"item\" index=\"index\" collection=\"deptName\"\n" +
                    " open=\"(\" separator=\",\" close=\")\">\n" +
                    " #{item, jdbcType=VARCHAR}\n" +
                    "</foreach>",
            "</if>",
            "<if test='name!=null and !name.isEmpty()'>",
            " and NAME LIKE concat('%',#{name},'%')",
            "</if>",
            "<if test='sex!=null and !sex.isEmpty()'>",
            " and SEX=#{sex}",
            "</if>",
            "<if test='idNo!=null and !idNo.isEmpty()'>",
            " and ID_NO LIKE concat('%',#{idNo},'%')",
            "</if>",
            "<if test='position!=null and !position.isEmpty()'>",
            " and POSITION LIKE concat('%',#{position},'%')",
            "</if>",
            "<if test='workType!=null and !workType.isEmpty()'>",
            " and WORK_TYPE LIKE concat('%',#{workType},'%')",
            "</if>",
            "<if test='calcType!=null and !calcType.isEmpty()'>",
            " and CALC_TYPE=#{calcType}",
            "</if>",
            "<if test='inTime!=null and outTime!=null'>",
            "and SYS_DATE BETWEEN #{inTime} AND #{outTime}",
            "</if>",
            "<if test='inTime!=null and outTime==null'>",
            "and SYS_DATE <![CDATA[ >=]]> #{inTime}",
            "</if>",
            "<if test='outTime!=null and inTime==null'>",
            "and SYS_DATE <![CDATA[ <=]]> #{outTime}",
            "</if>",
            "<if test='address!=null and !address.isEmpty()'>",
            "and ADDRESS LIKE CONCAT('%',#{address},'%')",
            "</if>",
            "<if test='change!=null and !change.isEmpty()'>",
            " and NEW_WORK_TYPE LIKE concat('%',#{change},'%')",
            "</if>",
            "</where>",
            "order by SYS_DATE desc",
            "</script>",
    })
    List<ChangeDto> getPcEmployeeWorkTypeChg(GetRequest request);

    /**
     * 离职写入记录表
     * @param pcEmployeeDelList
     */
    @Insert({
            "<script>",
            "INSERT INTO PC_EMPLOYEE_DEL (COMPANY_ID, DEPT_NO, DEPT_NAME, NAME, SEX, ID_NO, POSITION, WORK_TYPE, CALC_TYPE, MOBILE_NO, ADDRESS, DATE_IN, CONTRACT, SYS_USER, SYS_DATE, DEL_DATE, DEL_REASON, WORKER_ID, BRISDAY, BANK_CARD_NAME, BANK_CARD_ID, BANK_CARD_USER, STATUS)" +
            "VALUES ",
            "<foreach collection='pcEmployeeDelList' item='t' index='index' separator=','>",
            "(#{t.companyId}, #{t.deptNo}, #{t.deptName}, #{t.name}, #{t.sex}, #{t.idNo}, #{t.position}, #{t.workType}, #{t.calcType}, #{t.mobileNo}, #{t.address}, #{t.dateIn}, #{t.contract}, #{t.sysUser}, #{t.sysDate}, #{t.delDate}," +
            "#{t.delReason}, #{t.workerId}, #{t.brisday}, #{t.bankCardName}, #{t.bankCardId}, #{t.bankCardUser}, #{t.status})",
            "</foreach>",
            "</script>",
    })
    void addPcEmployeeDel(@Param("pcEmployeeDelList") List<PcEmployeeDel> pcEmployeeDelList);

    /**
     * 根据工号删除员工
     * @param workerIdList
     */
    @Delete({
            "<script>",
            "DELETE FROM PC_EMPLOYEE WHERE WORKER_ID IN",
            "<foreach item='t' collection='workerIdList' open='(' separator=',' close=')'>",
            "#{t}",
            "</foreach>",
            "</script>"
    })
    void delToPcEmployee(@Param("workerIdList") List<String> workerIdList);

    /**
     * 员工离职资料查询
     * @param request
     * @return
     */
    @Select({
            "<script>",
            " SELECT DEL_DATE as sysDate,DEL_REASON as `change`,DEPT_NAME,NAME,SEX,ID_NO,POSITION,WORK_TYPE,CALC_TYPE,MOBILE_NO,ADDRESS,DATE_IN,CONTRACT FROM PC_EMPLOYEE_DEL",
            "<where>",
            "<if test='companyId!=null and !companyId.isEmpty()'>",
            " and COMPANY_ID=#{companyId}",
            "</if>",
            "<if test='deptName!=null and deptName.size()>0'>",
            " and DEPT_NAME in <foreach item=\"item\" index=\"index\" collection=\"deptName\"\n" +
                    " open=\"(\" separator=\",\" close=\")\">\n" +
                    " #{item, jdbcType=VARCHAR}\n" +
                    "</foreach>",
            "</if>",
            "<if test='name!=null and !name.isEmpty()'>",
            " and NAME LIKE concat('%',#{name},'%')",
            "</if>",
            "<if test='sex!=null and !sex.isEmpty()'>",
            " and SEX=#{sex}",
            "</if>",
            "<if test='idNo!=null and !idNo.isEmpty()'>",
            " and ID_NO LIKE concat('%',#{idNo},'%')",
            "</if>",
            "<if test='position!=null and !position.isEmpty()'>",
            " and POSITION LIKE concat('%',#{position},'%')",
            "</if>",
            "<if test='workType!=null and !workType.isEmpty()'>",
            " and WORK_TYPE LIKE concat('%',#{workType},'%')",
            "</if>",
            "<if test='calcType!=null and !calcType.isEmpty()'>",
            " and CALC_TYPE=#{calcType}",
            "</if>",
            "<if test='inTime!=null and outTime!=null'>",
            "and DEL_DATE BETWEEN #{inTime} AND #{outTime}",
            "</if>",
            "<if test='inTime!=null and outTime==null'>",
            "and DEL_DATE <![CDATA[ >=]]> #{inTime}",
            "</if>",
            "<if test='outTime!=null and inTime==null'>",
            "and DEL_DATE <![CDATA[ <=]]> #{outTime}",
            "</if>",
            "<if test='address!=null and !address.isEmpty()'>",
            "and ADDRESS LIKE CONCAT('%',#{address},'%')",
            "</if>",
            "<if test='change!=null and !change.isEmpty()'>",
            " and DEL_REASON LIKE concat('%',#{change},'%')",
            "</if>",
            "</where>",
            "order by DEL_DATE desc",
            "</script>",
    })
    List<ChangeDto> getPcEmployeeDel(GetRequest request);

    /**
     * 根据身份证查询
     * @param idNo
     * @return
     */
    @Select("select * from PC_EMPLOYEE_DEL where ID_NO=#{idNo}")
    PcEmployeeDel getIdNoToPcEmployeeDel(String idNo);

    /**
     * 删除员工离职表中数据
     * @param idNo
     */
    @Delete("delete from PC_EMPLOYEE_DEL where ID_NO=#{idNo}")
    void delPcEmployeeDel(String idNo);

    /**
     * 根据查询导入数据
     * @param request
     * @return
     */
    @Select({
            "<script>",
            " SELECT DEL_DATE,DEL_REASON,WORKER_ID,DEPT_NAME,NAME,SEX,ID_NO,POSITION,WORK_TYPE,CALC_TYPE,MOBILE_NO,ADDRESS,DATE_IN,CONTRACT,BANK_CARD_NAME,BANK_CARD_ID,BANK_CARD_USER FROM PC_EMPLOYEE_DEL",
            "<where>",
            "<if test='companyId!=null and !companyId.isEmpty()'>",
            " and COMPANY_ID=#{companyId}",
            "</if>",
            "<if test='deptName!=null and deptName.size()>0'>",
            " and DEPT_NAME in <foreach item=\"item\" index=\"index\" collection=\"deptName\"\n" +
                    " open=\"(\" separator=\",\" close=\")\">\n" +
                    " #{item, jdbcType=VARCHAR}\n" +
                    "</foreach>",
            "</if>",
            "<if test='name!=null and !name.isEmpty()'>",
            " and NAME LIKE concat('%',#{name},'%')",
            "</if>",
            "<if test='sex!=null and !sex.isEmpty()'>",
            " and SEX=#{sex}",
            "</if>",
            "<if test='idNo!=null and !idNo.isEmpty()'>",
            " and ID_NO LIKE concat('%',#{idNo},'%')",
            "</if>",
            "<if test='position!=null and !position.isEmpty()'>",
            " and POSITION LIKE concat('%',#{position},'%')",
            "</if>",
            "<if test='workType!=null and !workType.isEmpty()'>",
            " and WORK_TYPE LIKE concat('%',#{workType},'%')",
            "</if>",
            "<if test='calcType!=null and !calcType.isEmpty()'>",
            " and CALC_TYPE=#{calcType}",
            "</if>",
            "<if test='inTime!=null and outTime!=null'>",
            "and DEL_DATE BETWEEN #{inTime} AND #{outTime}",
            "</if>",
            "<if test='inTime!=null and outTime==null'>",
            "and DEL_DATE <![CDATA[ >=]]> #{inTime}",
            "</if>",
            "<if test='outTime!=null and inTime==null'>",
            "and DEL_DATE <![CDATA[ <=]]> #{outTime}",
            "</if>",
            "<if test='address!=null and !address.isEmpty()'>",
            "and ADDRESS LIKE CONCAT('%',#{address},'%')",
            "</if>",
            "<if test='change!=null and !change.isEmpty()'>",
            " and DEL_REASON LIKE concat('%',#{change},'%')",
            "</if>",
            "</where>",
            "order by DEL_DATE desc",
            "</script>",
    })
    List<PcEmployeeDelDto> getPcEmployeeDelList(GetRequest request);

    /**
     * 根据身份证查询变更前信息
     * @param idNo
     * @return
     */
    @Select("SELECT NEW_BANK_CARD FROM PC_EMPLOYEE_BANK_CARD_CHG where ID_NO=#{idNo}  ORDER BY ABS(TIMESTAMPDIFF(SECOND, SYS_DATE, NOW())) LIMIT 1;")
    String getPcEmployeeBankCardChgToIdNo(String idNo);

    /**
     * 新增银行变更记录
     * @param list
     */
    @Insert({
            "<script>",
            "INSERT INTO PC_EMPLOYEE_BANK_CARD_CHG (COMPANY_ID, DEPT_NO, DEPT_NAME, NAME, SEX, ID_NO, POSITION, WORK_TYPE, CALC_TYPE, MOBILE_NO, ADDRESS, DATE_IN, CONTRACT, SYS_USER, SYS_DATE, NEW_BANK_CARD)" +
            "VALUES ",
            "<foreach collection='list' item='t' index='index' separator=','>",
            "(#{t.companyId}, #{t.deptNo}, #{t.deptName}, #{t.name}, #{t.sex}, #{t.idNo}, #{t.position}, #{t.workType}, #{t.calcType}, #{t.mobileNo}, #{t.address}, #{t.dateIn}, #{t.contract}, #{t.sysUser}, #{t.sysDate}, #{t.newBankCard})",
            "</foreach>",
            "</script>",
    })
    void addPcEmployeeBankCardChg(@Param("list") List<PcEmployeeBankCardChg> list);

    /**
     * 修改银行信息
     * @param workerId
     * @param bankCardId
     * @param bankCardName
     * @param bankCardUser
     */
    @Update({
            "<script>",
            "update PC_EMPLOYEE set BANK_CARD_NAME=#{bankCardName},BANK_CARD_ID=#{bankCardId},BANK_CARD_USER=#{bankCardUser} where WORKER_ID in ",
            "<foreach item='t' collection='workerId' open='(' separator=',' close=')'>",
            "#{t}",
            "</foreach>",
            "</script>"
    })
    void updateBrandToPcEmployee(@Param("workerId") List<String> workerId,
                                 @Param("bankCardId") String bankCardId,
                                 @Param("bankCardName") String bankCardName,
                                 @Param("bankCardUser") String bankCardUser);

    /**
     * 银行变更信息查询
     * @param request
     * @return
     */
    @Select({
            "<script>",
            " SELECT SYS_DATE,NEW_BANK_CARD as `change`,DEPT_NAME,NAME,SEX,ID_NO,POSITION,WORK_TYPE,CALC_TYPE,MOBILE_NO,ADDRESS,DATE_IN,CONTRACT FROM PC_EMPLOYEE_BANK_CARD_CHG",
            "<where>",
            "<if test='companyId!=null and !companyId.isEmpty()'>",
            " and COMPANY_ID=#{companyId}",
            "</if>",
            "<if test='deptName!=null and deptName.size()>0'>",
            " and DEPT_NAME in <foreach item=\"item\" index=\"index\" collection=\"deptName\"\n" +
                    " open=\"(\" separator=\",\" close=\")\">\n" +
                    " #{item, jdbcType=VARCHAR}\n" +
                    "</foreach>",
            "</if>",
            "<if test='name!=null and !name.isEmpty()'>",
            " and NAME LIKE concat('%',#{name},'%')",
            "</if>",
            "<if test='sex!=null and !sex.isEmpty()'>",
            " and SEX=#{sex}",
            "</if>",
            "<if test='idNo!=null and !idNo.isEmpty()'>",
            " and ID_NO LIKE concat('%',#{idNo},'%')",
            "</if>",
            "<if test='position!=null and !position.isEmpty()'>",
            " and POSITION LIKE concat('%',#{position},'%')",
            "</if>",
            "<if test='workType!=null and !workType.isEmpty()'>",
            " and WORK_TYPE LIKE concat('%',#{workType},'%')",
            "</if>",
            "<if test='calcType!=null and !calcType.isEmpty()'>",
            " and CALC_TYPE=#{calcType}",
            "</if>",
            "<if test='inTime!=null and outTime!=null'>",
            "and SYS_DATE BETWEEN #{inTime} AND #{outTime}",
            "</if>",
            "<if test='inTime!=null and outTime==null'>",
            "and SYS_DATE <![CDATA[ >=]]> #{inTime}",
            "</if>",
            "<if test='outTime!=null and inTime==null'>",
            "and SYS_DATE <![CDATA[ <=]]> #{outTime}",
            "</if>",
            "<if test='address!=null and !address.isEmpty()'>",
            "and ADDRESS LIKE CONCAT('%',#{address},'%')",
            "</if>",
            "<if test='change!=null and !change.isEmpty()'>",
            " and NEW_BANK_CARD LIKE concat('%',#{change},'%')",
            "</if>",
            "</where>",
            "order by SYS_DATE desc",
            "</script>",
    })
    List<ChangeDto> getPcEmployeeBankCardChgList(GetRequest request);

    /**
     * 员工职务变更资料明细
     * @param request
     * @return
     */
    @Select({
            "<script>",
            "SELECT t.NAME,t.SEX,t.ID_NO,t.DEPT_NAME,t.POSITION,t.WORK_TYPE,t.SYS_DATE,t.newCalcType,t.`change`,t.CALC_TYPE,t.MOBILE_NO,t.ADDRESS,t.DATE_IN,"+
                    "t1.DEL_DATE,t.CONTRACT from PC_EMPLOYEE_DEL t1 RIGHT JOIN"+
                    "(SELECT COMPANY_ID,DEPT_NAME,NAME,SEX,ID_NO,POSITION,WORK_TYPE,SYS_DATE,NEW_WORK_TYPE as`change`,'工种变更' as newCalcType,CALC_TYPE,MOBILE_NO,ADDRESS,DATE_IN,"+
                    "CONTRACT FROM PC_EMPLOYEE_WORK_TYPE_CHG UNION ALL "+
                    "SELECT COMPANY_ID,DEPT_NAME,NAME,SEX,ID_NO,POSITION,WORK_TYPE,SYS_DATE,NEW_POSITION as`change`,'职务变更' as newCalcType,CALC_TYPE,MOBILE_NO,ADDRESS,DATE_IN,"+
                    "CONTRACT FROM PC_EMPLOYEE_POSITION_CHG UNION ALL "+
                    "SELECT COMPANY_ID,DEPT_NAME,NAME,SEX,ID_NO,POSITION,WORK_TYPE,SYS_DATE,NEW_DEPT_NAME as`change`,'部门变更'as newCalcType,CALC_TYPE,MOBILE_NO,ADDRESS,DATE_IN,"+
                    "CONTRACT FROM PC_EMPLOYEE_DEPT_CHG )t on t1.ID_NO=t.ID_NO",
            "<where>",
            "<if test='companyId!=null and !companyId.isEmpty()'>",
            " and t.COMPANY_ID=#{companyId}",
            "</if>",
            "<if test='deptName!=null and deptName.size()>0'>",
            " and t.DEPT_NAME in <foreach item=\"item\" index=\"index\" collection=\"deptName\"\n" +
                    " open=\"(\" separator=\",\" close=\")\">\n" +
                    " #{item, jdbcType=VARCHAR}\n" +
                    "</foreach>",
            "</if>",
            "<if test='change!=null and !change.isEmpty()'>",
            " and t.`change` LIKE concat('%',#{change},'%')",
            "</if>",
            "<if test='name!=null and !name.isEmpty()'>",
            " and t.NAME LIKE concat('%',#{name},'%')",
            "</if>",
            "<if test='sex!=null and !sex.isEmpty()'>",
            " and t.SEX=#{sex}",
            "</if>",
            "<if test='type!=null and type==1'>",
            " and t.newCalcType='职务变更'",
            "</if>",
            "<if test='type!=null and type==2'>",
            " and t.newCalcType='部门变更'",
            "</if>",
            "<if test='type!=null and type==3'>",
            " and t.newCalcType='工种变更'",
            "</if>",
            "<if test='idNo!=null and !idNo.isEmpty()'>",
            " and t.ID_NO LIKE concat('%',#{idNo},'%')",
            "</if>",
            "<if test='position!=null and !position.isEmpty()'>",
            " and t.POSITION LIKE concat('%',#{position},'%')",
            "</if>",
            "<if test='workType!=null and !workType.isEmpty()'>",
            " and t.WORK_TYPE LIKE concat('%',#{workType},'%')",
            "</if>",
            "<if test='calcType!=null and !calcType.isEmpty()'>",
            " and t.CALC_TYPE=#{calcType}",
            "</if>",
            "<if test='inTime!=null and outTime!=null'>",
            "and t.SYS_DATE BETWEEN #{inTime} AND #{outTime}",
            "</if>",
            "<if test='inTime!=null and outTime==null'>",
            "and t.SYS_DATE <![CDATA[ >=]]> #{inTime}",
            "</if>",
            "<if test='outTime!=null and inTime==null'>",
            "and t.SYS_DATE <![CDATA[ <=]]> #{outTime}",
            "</if>",
            "<if test='inLeaveTime!=null and outLeaveTime!=null'>",
            "and t1.DEL_DATE BETWEEN #{inLeaveTime} AND #{outLeaveTime}",
            "</if>",
            "<if test='inLeaveTime!=null and outLeaveTime==null'>",
            "and t1.DEL_DATE <![CDATA[ >=]]> #{inLeaveTime}",
            "</if>",
            "<if test='outLeaveTime!=null and inLeaveTime==null'>",
            "and t1.DEL_DATE <![CDATA[ <=]]> #{outLeaveTime}",
            "</if>",
            "<if test='address!=null and !address.isEmpty()'>",
            "and t.ADDRESS LIKE CONCAT('%',#{address},'%')",
            "</if>",
            "</where>",
            "ORDER BY t.SYS_DATE DESC",
            "</script>",
    })
    List<ChangeDto> getEmployeeChangesList(EmployeeChangesRequest request);

    /**
     * 根据工号和姓名查询员工信息
     * @param workerId
     * @param workerName
     * @return
     */
    @Select("SELECT t.DEPT_NAME from (SELECT DEPT_NAME,WORKER_ID,NAME FROM PC_EMPLOYEE UNION ALL SELECT DEPT_NAME,WORKER_ID,NAME from PC_EMPLOYEE_DEL) t where WORKER_ID=#{workerId} and NAME=#{workerName}")
    String getEmployeeByWorkerId(@Param("workerId") String workerId,@Param("workerName")String workerName);

    /**
     * 写入工资表
     * @param pcBasicSalary
     */
    @Insert("INSERT INTO PC_BASIC_SALARY" +
            "(COMPANY_ID, DEPT_NO, DEPT_NAME, NAME, SEX, ID_NO, POSITION, WORK_TYPE," +
            "DATE_IN, SYS_USER, SYS_DATE, CALC_TYPE, WORKER_ID, DEL_DATE, DEL_REASON," +
            "FACTORY, BASIC_SALARY, POSITION_ALW, MIN_SALARY, MIN_YYYYMM, BANK_CARD_ID," +
            "MIN_YYYYMM2, UNION_M, HEALTH_M, UPDATE_DATE, SALARY_ALW, P_DEPT_NO, P_DEPT_NAME)" +
            "VALUES" +
            "(#{companyId}, #{deptNo}, #{deptName}, #{name}, #{sex}, #{idNo}, #{position}," +
            "#{workType}, #{dateIn}, #{sysUser}, #{sysDate}, #{calcType}, #{workerId}," +
            "#{delDate}, #{delReason}, #{factory}, #{basicSalary}, #{positionAlw}," +
            "#{minSalary}, #{minYyyymm}, #{bankCardId}, #{minYyyymm2}, #{unionM}," +
            "#{healthM}, #{updateDate}, #{salaryAlw}, #{pDeptNo}, #{pDeptName})")
    void addPcBasicSalary(PcBasicSalary pcBasicSalary);

    /**
     * 根据工号修改
     * @param salary 工资
     * @param now 修改时间
     * @param workType 工种
     * @param workerIdList 工号集合
     */
    @Update({
            "<script>",
            "update PC_BASIC_SALARY set WORK_TYPE = #{workType},UPDATE_DATE=#{now},BASIC_SALARY=#{salary} where WORKER_ID in ",
            "<foreach item='t' collection='workerId' open='(' separator=',' close=')'>",
            "#{t}",
            "</foreach>",
            "</script>"
    })
    void updateWorkType(@Param("salary") BigDecimal salary,@Param("now") LocalDateTime now,@Param("workType") String workType,@Param("workerId") List<String> workerIdList);

    /**
     * 修改工资表中离职原因
     * @param delReason 离职原因
     * @param delDate 离职日期
     * @param workerIdList 工号集合
     * @param now 修改时间
     */
    @Update({
            "<script>",
            "update PC_BASIC_SALARY set DEL_REASON = #{delReason},DEL_DATE=#{delDate},UPDATE_DATE=#{now} where WORKER_ID in ",
            "<foreach item='t' collection='workerId' open='(' separator=',' close=')'>",
            "#{t}",
            "</foreach>",
            "</script>"
    })
    void updatePcBasicSalaryToDelReason(@Param("delReason") String delReason,@Param("delDate") LocalDateTime delDate,@Param("workerId") List<String> workerIdList, @Param("now") LocalDateTime now);

    /**
     * 修改工资表中职务
     * @param position 职务
     * @param now 修改时间
     * @param workerIdList 工号集合
     */
    @Update({
            "<script>",
            "update PC_BASIC_SALARY set POSITION = #{position},UPDATE_DATE=#{now} where WORKER_ID in ",
            "<foreach item='t' collection='workerId' open='(' separator=',' close=')'>",
            "#{t}",
            "</foreach>",
            "</script>"
    })
    void updatePcBasicSalaryToPosition(@Param("position") String position,@Param("now") LocalDateTime now,@Param("workerId") List<String> workerIdList);

    /**
     * 修改工资表中部门
     * @param deptNo
     * @param deptName
     * @param now
     * @param workerIdList
     */
    @Update({
            "<script>",
            "update PC_BASIC_SALARY set DEPT_NO = #{deptNo},DEPT_NAME = #{deptName},UPDATE_DATE=#{now} where WORKER_ID in ",
            "<foreach item='t' collection='workerId' open='(' separator=',' close=')'>",
            "#{t}",
            "</foreach>",
            "</script>"
    })
    void updatePcBasicSalaryToDept(@Param("deptNo") String deptNo, @Param("deptName")String deptName,@Param("now") LocalDateTime now,@Param("workerId") List<String> workerIdList);

    /**
     * 修改工资表中计件计时
     * @param calcType 计算类型
     * @param now 修改时间
     * @param workerId 工号
     */
    @Update("update PC_BASIC_SALARY set CALC_TYPE = #{calcType},UPDATE_DATE=#{now} where WORKER_ID=#{workerId}")
    void updatePcBasicSalaryCeleType(@Param("calcType") String calcType,@Param("now") LocalDateTime now,@Param("workerId") String workerId);

    /**
     * 查询当前月份变更记录
     * @param years
     * @param companyId
     * @return
     */
    List<PcEmployeeWorkTypeChg> getEmployeeWorkTypeChgList(String years,String companyId);

    /**
     * 根据工号查询当前月份变更记录
     * @param request
     * @return
     */
    List<PcEmployeeWorkTypeChg> getEmployeeWorkTypeChgList2(@Param("list") List<UpdateRemarkRequest> request);

    /*根据部门编号去查询员工*/
    List<PcEmployeeDto> getStitchEmployee(String companyId, String workName, String workId, List<String> depetList);
}
